library(tidyverse)
## ── Attaching packages ─────────────────────────────────────── tidyverse 1.3.1 ──
## ✓ ggplot2 3.3.5     ✓ purrr   0.3.4
## ✓ tibble  3.1.5     ✓ dplyr   1.0.7
## ✓ tidyr   1.1.4     ✓ stringr 1.4.0
## ✓ readr   2.0.2     ✓ forcats 0.5.1
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## x dplyr::filter() masks stats::filter()
## x dplyr::lag()    masks stats::lag()
boston_original_df <- read_csv("Data/zillow-boston.csv")
## Rows: 836 Columns: 20
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr   (8): address, street_name, city, rstate, postcode, property_type, prop...
## dbl  (11): property_id, latitude, longitude, price, zestimate, bedroom_numbe...
## dttm  (1): RunDate
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
view(boston_original_df)
boston_MA_df <- boston_original_df %>% filter(rstate == "MA")
boston_MA_df <- boston_MA_df %>% arrange(desc(price))
view(boston_MA_df)

##Creation of new variables

###Cap rate data for boston https://blogs.umass.edu/kmfay/2022/02/21/quincy-ma-average-property-noi/ according to this blog from Umass Boston the average NOI for Boston homes is $6,915

boston_add_df <- boston_MA_df %>% mutate(cap_rate = 6915/price) %>%
  filter(!is.na(bedroom_number)) %>% mutate(price_per_bedroom = price/bedroom_number)
boston_add_df
## # A tibble: 766 × 22
##    property_id address     street_name  city  rstate latitude longitude postcode
##          <dbl> <chr>       <chr>        <chr> <chr>     <dbl>     <dbl> <chr>   
##  1    59166243 30 Chestnu… Chestnut St  Bost… MA         42.4     -71.1 02108   
##  2  2107626553 63 Mount V… Mount Verno… Bost… MA         42.4     -71.1 02108   
##  3    59166308 17 Louisbu… Louisburg Sq Bost… MA         42.4     -71.1 02108   
##  4  2117794141 318 Beacon… Beacon St    Bost… MA         42.4     -71.1 02116   
##  5   333605731 1 Dalton S… Dalton St #… Bost… MA         42.3     -71.1 02115   
##  6    90141410 776 Boylst… Boylston St… Bost… MA         42.3     -71.1 02199   
##  7    59163970 10 Rowes W… Rowes Wharf… Bost… MA         42.4     -71.1 02110   
##  8    90141331 776 Boylst… Boylston St… Bost… MA         42.3     -71.1 02199   
##  9    61319917 2 Avery St… Avery St AP… Bost… MA         42.4     -71.1 02111   
## 10  2075168741 2 Avery St… Avery St AP… Bost… MA         NA        NA   02111   
## # … with 756 more rows, and 14 more variables: price <dbl>, zestimate <dbl>,
## #   bedroom_number <dbl>, bathroom_number <dbl>, price_per_unit <dbl>,
## #   living_space <dbl>, land_space <dbl>, property_type <chr>,
## #   property_status <chr>, agency_name <chr>, zillow_owned <dbl>,
## #   RunDate <dttm>, cap_rate <dbl>, price_per_bedroom <dbl>
ggplot(data = boston_add_df, aes(x = bedroom_number, y = price)) +
  geom_col() +
  facet_wrap( ~ property_type)

boston_multifamily <- boston_add_df %>% filter(property_type == "MULTI_FAMILY") %>%
  mutate(avg_price = sum(price) / 38) %>%
  mutate(projected_rental_income = living_space * 4.50) %>%
  mutate(down_payment = price * .20) %>%
  mutate(principal = price - down_payment) %>%
  mutate(projected_mortgage_payment = ((principal/330) * 1.042)) %>%
  mutate(projected_mortgage_yr = projected_mortgage_payment * 12) %>%
  mutate(projected_rental_income_yr = (projected_rental_income * 12)) %>%
  mutate(ROI = (projected_rental_income_yr - projected_mortgage_yr) / projected_mortgage_yr)



boston_multifamily
## # A tibble: 38 × 30
##    property_id address      street_name city  rstate latitude longitude postcode
##          <dbl> <chr>        <chr>       <chr> <chr>     <dbl>     <dbl> <chr>   
##  1  2117794141 318 Beacon … Beacon St   Bost… MA         42.4     -71.1 02116   
##  2  2109488118 37 Joy St, … Joy St      Bost… MA         42.4     -71.1 02114   
##  3  2107682803 7 Anderson … Anderson St Bost… MA         NA        NA   02114   
##  4  2067588931 67-69 W Ced… 69 W Cedar… Bost… MA         NA        NA   02114   
##  5  2067247949 44-46 Lexin… 46 Lexingt… Bost… MA         42.4     -71.0 02128   
##  6  2066837539 9R Trenton … Trenton St  Bost… MA         42.4     -71.0 02128   
##  7  2066559353 538 Tremont… Tremont St… Bost… MA         42.3     -71.1 02118   
##  8  2079779704 47 Revere S… Revere St   Bost… MA         42.4     -71.1 02114   
##  9  2102669520 304 Shawmut… Shawmut Ave Bost… MA         42.3     -71.1 02118   
## 10  2099167865 489 E Broad… E Broadway  Bost… MA         42.3     -71.0 02127   
## # … with 28 more rows, and 22 more variables: price <dbl>, zestimate <dbl>,
## #   bedroom_number <dbl>, bathroom_number <dbl>, price_per_unit <dbl>,
## #   living_space <dbl>, land_space <dbl>, property_type <chr>,
## #   property_status <chr>, agency_name <chr>, zillow_owned <dbl>,
## #   RunDate <dttm>, cap_rate <dbl>, price_per_bedroom <dbl>, avg_price <dbl>,
## #   projected_rental_income <dbl>, down_payment <dbl>, principal <dbl>,
## #   projected_mortgage_payment <dbl>, projected_mortgage_yr <dbl>, …

###Links mutate(ROI = ((projected_rental_income_yr - projected_mortage_yr) / projected_mortgage_yr)) ###average rent per square foot https://www.statista.com/statistics/879118/rent-per-square-foot-in-apartments-by-state-usa/

https://www.rentcafe.com/average-rent-market-trends/us/ma/boston/

library(plotly)
## 
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
## 
##     last_plot
## The following object is masked from 'package:stats':
## 
##     filter
## The following object is masked from 'package:graphics':
## 
##     layout
plot1 <- ggplot(data = boston_multifamily, aes(x = living_space, y = price, 
                                               label = address)) +
  geom_point() +
  geom_smooth()

ggplotly(plot1, tooltip = 'label')
## `geom_smooth()` using method = 'loess' and formula 'y ~ x'
plot2 <- ggplot(data = boston_multifamily, aes(x = price, y = projected_rental_income, 
                                               label = address)) +
  geom_point() 
ggplotly(plot2, tooltip = 'label')
plot3 <- ggplot(data = boston_multifamily, aes(x = projected_mortgage_yr, y = ROI, 
                                               label = address)) +
  geom_point() 
ggplotly(plot3, tooltip = 'label')
Table continues below
address price down_payment principal
318 Beacon St, Boston, MA 02116 13750000 2750000 1.1e+07
37 Joy St, Boston, MA 02114 6900000 1380000 5520000
7 Anderson St, Boston, MA 02114 6470000 1294000 5176000
67-69 W Cedar St #71, Boston, MA 02114 5400000 1080000 4320000
44-46 Lexington St, Boston, MA 02128 5250000 1050000 4200000
9R Trenton St, Boston, MA 02128 4900000 980000 3920000
538 Tremont St #1-4, Boston, MA 02118 4750000 950000 3800000
47 Revere St, Boston, MA 02114 4250000 850000 3400000
304 Shawmut Ave, Boston, MA 02118 4200000 840000 3360000
489 E Broadway, Boston, MA 02127 3795000 759000 3036000
58 Prince St, Boston, MA 02113 3600000 720000 2880000
506 E Broadway, Boston, MA 02127 3445000 689000 2756000
592A E Broadway, Boston, MA 02127 3199000 639800 2559200
820-824 Huntington Ave, Boston, MA 02115 2999000 599800 2399200
28-30 Mercer St, Boston, MA 02127 2999000 599800 2399200
12 O St, Boston, MA 02127 2950000 590000 2360000
32 Parmenter St, Boston, MA 02113 2500000 5e+05 2e+06
21 Henchman St, Boston, MA 02113 2500000 5e+05 2e+06
187 Webster St, Boston, MA 02128 2486886 497377 1989509
163-165A Salem St, Boston, MA 02113 2350000 470000 1880000
226-228 Princeton St, Boston, MA 02128 2295000 459000 1836000
584-586 E Broadway, Boston, MA 02127 2e+06 4e+05 1600000
222 Calumet St, Boston, MA 02120 1887500 377500 1510000
46 Sheridan St, Boston, MA 02130 1875000 375000 1500000
98-100 Marginal St, Boston, MA 02128 1799999 360000 1439999
853 E 4th St, Boston, MA 02127 1750000 350000 1400000
951 Blue Hill Ave, Boston, MA 02124 1649900 329980 1319920
57 Bennington St, Boston, MA 02128 1550000 310000 1240000
43 School St, Boston, MA 02129 1499000 299800 1199200
6 Danny Rd, Boston, MA 02136 1499000 299800 1199200
126 Marion St, Boston, MA 02128 1489000 297800 1191200
9 Boston St, Boston, MA 02127 1435000 287000 1148000
465 E 8th St, Boston, MA 02127 1399000 279800 1119200
3381 Washington St, Boston, MA 02130 1395000 279000 1116000
87-89 Nottinghill Rd, Boston, MA 02135 999000 199800 799200
3 Magnolia Pl, Boston, MA 02125 950000 190000 760000
21 Forest St #23, Boston, MA 02119 850000 170000 680000
151 Townsend St, Boston, MA 02121 780000 156000 624000
projected_mortgage_yr projected_rental_income_yr ROI
416800 567000 0.3604
209158 444528 1.125
196123 406080 1.071
163689 421200 1.573
159142 569322 2.577
148532 454518 2.06
143985 237600 0.6502
128829 241164 0.872
127313 209088 0.6423
115037 323460 1.812
109126 251910 1.308
104427 320112 2.065
96970 238950 1.464
90908 255960 1.816
90908 431622 3.748
89423 200556 1.243
75782 206064 1.719
75782 154656 1.041
75384 201960 1.679
71235 167400 1.35
69568 377298 4.423
60625 179928 1.968
57215 123552 1.159
56836 135108 1.377
54563 115614 1.119
53047 147150 1.774
50013 294030 4.879
46985 270000 4.747
45439 113508 1.498
45439 218862 3.817
45136 239760 4.312
43499 131328 2.019
42408 83538 0.9699
42286 171072 3.046
30282 133812 3.419
28797 98820 2.432
25766 123552 3.795
23644 299268 11.66